--------------------------------------------------------------------------------
-- @name: how density is calculated
-- @author: dion cho
-- @note: test case for the calculation of the column density
--------------------------------------------------------------------------------

set pages 100
set lines 150
set trimspool on
set timing off

drop table t1 purge;

create table t1
as
select
   -- no histogram
   level c1,
   -- frequency histogram
   case
       when level between 1 and 10000 then 1
       when level between 10001 and 15000 then 2
       when level between 15001 and 20000 then 3
   end as c2,
   -- height-balanced histogram
   case
       when level between 1 and 10000 then 1
       when level between 10001 and 15000 then 2
       when level between 15001 and 20000 then level
   end as c3
from dual
connect by level <= 20000;

exec dbms_stats.gather_table_stats(user, 't1', estimate_percent=>100, -
  method_opt=>'for all columns size skewonly');

@tab_stat t1

-- column c1 : no histogram
select 1 / 20000 from dual;


-- column c2 : FREQUENCY histogram
select 1 / (2000 * 2) from dual;


-- column c3 : HEIGHT-BALANCED histogram
select 
  ((1*1) * 5000) / (5000 * 20000) 
from dual;


-- for FREQUENCY histogram
-- existent value
explain plan for
select * from t1 where c2 = 1;

@plan

-- unknown value(bind)
explain plan for
select * from t1 where c2 = :b1;

@plan

-- non existent value within range
explain plan for
select * from t1 where c2 = 2.5;

@plan

-- non existent value out of range
explain plan for
select * from t1 where c2 = 3.1;

@plan


-- non existent value out of range
explain plan for
select * from t1 where c2 = 4;

@plan



-- non existent value out of range
explain plan for
select * from t1 where c2 = 100;

@plan

-- for HEIGHT-BALANCED histogram
-- existent value
explain plan for
select * from t1 where c3 = 1;

@plan

-- unknown value(bind)
explain plan for
select * from t1 where c3 = :b1;

@plan

-- non existent value within range
explain plan for
select * from t1 where c3 = 2.5;

@plan

-- non existent value out of range
explain plan for
select * from t1 where c3 = 100000;

@plan


-- check 10053
@plan
alter session set "_optim_peek_user_binds" = false;
var b1 number;
exec :b1 := 1;

alter session set tracefile_identifier = 'case1';
@trace_on 10053 1
select * from t1 where c2 = :b1;
@trace_off
@trace_view

alter session set tracefile_identifier = 'case2';
@trace_on 10053 1
select * from t1 where c3 = :b1;
@trace_off
@trace_view




set timing on
